#Copyright (c) 2025, Alibaba Cloud and its affiliates;
#Licensed under the Apache License, Version 2.0 (the "License");
#you may not use this file except in compliance with the License.
#You may obtain a copy of the License at

#   http://www.apache.org/licenses/LICENSE-2.0

#Unless required by applicable law or agreed to in writing, software
#distributed under the License is distributed on an "AS IS" BASIS,
#WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#See the License for the specific language governing permissions and
#limitations under the License.



import os
import re 
import sqlite3

#con = sqlite3.connect('/usr/local/fce/fce-server/sqlite.db')

table = [
    {
    'operation': 'add',
    'table': 'auth_token',
    'field_name': 'user',
    'field_type': 'VARCHAR(255)'    
    },
]


modify_job_templates = []


def db_execute(db, sql):
    cur = db.cursor()
    cur.execute(sql)
    db.commit()
    return cur.fetchall()


def add_sql(item, res_table, res_field):
    if res_table and not res_field:
        return 'ALTER TABLE {} ADD COLUMN {} {};'.format(item['table'], item['field_name'], item.get('field_type',
                                                                                                     'VARCHAR(255)'))
    return ''


def drop_sql(item, res_table):
    if res_table:
        return 'DROP TABLE {};'.format(item['table'])
    return ''


def modify_sql(item, res_table, res_field, res_unique, sql_list):
    if res_table:
        if not res_field and not res_unique:
            for i in modify_job_templates:
                sql_list.append(i)
    return sql_list


def sql_generate(con, table):
    sql_list = []
    for item in table:
        check_table = 'SELECT count(*) FROM sqlite_master WHERE type="table" AND name = "{}";'.format(item['table'])
        check_field = "SELECT count(*) FROM sqlite_master WHERE name='{}' AND SQL LIKE '%{} %';".format(item['table'],
                                                                                                    item['field_name'])
        check_unique = "PRAGMA index_list({});".format(item['table'])

        res_table = bool(db_execute(con, check_table)[0][0])
        res_field = bool(db_execute(con, check_field)[0][0])
        res_unique = bool(re.search(item['field_name'], str(db_execute(con, check_unique))))

        if item['operation'] == 'add':
            sql_list.append(add_sql(item, res_table, res_field))
        if item['operation'] == 'drop':
            sql_list.append(drop_sql(item, res_table))
        if item['operation'] == 'modify':
            sql_list=modify_sql(item, res_table, res_field, res_unique, sql_list)

    return sql_list


def init_correct_sqlite(sqlite_db_path):
    if os.path.exists(sqlite_db_path):
        con = sqlite3.connect(sqlite_db_path)
        sql_list = sql_generate(con, table)
        for sql in sql_list:
            res = db_execute(con, sql)
        con.close()